<%@ page language="java" pageEncoding="UTF-8" isELIgnored="false"%>
<%@ page import="com.fly.common.dbtool.DBToolBOImpl,org.apache.commons.lang3.StringUtils"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%
    //测试
			session.setAttribute("admin", "admin");
%>
<c:if test="${empty sessionScope.admin}">
	<c:redirect url="/login.jsp" />
</c:if>
<html>
<head>
<title>数据库工具</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<style type="text/css">
body, th, td {
	font-size: 12px;
}
</style>
<script type="text/javascript" language="javascript">
	var keyword = /(select|update|delete|insert|from|order|by|group|asc|desc|left|right|inner|join|where|between|connect|prior|start|with|count|avg|sum|distinct|sysdate|to_char|to_date)[\(?|\s?|,?]/g;

	function keywordToUpper(str) {
		if (str != null && str != '' && str.length > 0) {
			return str.replace(keyword, function($1) {
				return $1.toUpperCase();
			});
		}
		return str;
	}

	function doExecute() {
		var sql = document.getElementById('sql');
		var frm = document.getElementById('frmDBTool');
		if (sql != null && frm != null) {
			if (sql.value != '') {
				var optType = document.getElementById('optType');
				if (sql.value.toLowerCase().match('select ')) {
					optType.value = 'R';
				} else if (sql.value.toLowerCase().match('insert ')) {
					optType.value = 'C';
				} else if (sql.value.toLowerCase().match('update ')) {
					optType.value = 'U';
				} else if (sql.value.toLowerCase().match('delete ')) {
					optType.value = 'D';
				} else if (sql.value.toLowerCase().match('alter ')) {
					optType.value = 'U';
				}
			}
		}
		frm.submit();
	}

	function onOptTypeChange() {
		var obj = document.getElementById('optType');
		if (obj != null) {
			var span = document.getElementById('spanPageSize');
			if (span != null) {
				if (obj.value == 'R') {
					span.style.display = 'block';
					onPageSizeChange();
				} else {
					span.style.display = 'none';
					var span2 = document.getElementById('spanPageNumber');
					if (span2 != null) {
						span2.style.display = 'none';
					}
				}
			}
		}
	}

	function onPageSizeChange() {
		var obj = document.getElementById('pageSize');
		if (obj != null) {
			var span = document.getElementById('spanPageNumber');
			if (span != null) {
				if (obj.value != '') {
					var optType = document.getElementById('optType');
					if (optType != null) {
						if (optType.value != 'R') {
							span.style.display = 'none';
						} else {
							span.style.display = 'block';
						}
					}
				} else {
					span.style.display = 'none';
				}
			}
		}
	}

	function setHTMLElementValue(elementId, value) {
		var elem = document.getElementById(elementId);
		if (elem != null) {
			elem.value = value;
		}
	}

	function setListValue(elementId, valueArray) {
		var list = document.getElementById(elementId);
		if (list == null || list == 'undefined') {
			return;
		}
		var valueType = typeof (valueArray);
		var value = [];
		if (valueType == 'string' || valueType == 'number') {
			value[0] = valueArray;
		} else {
			for (var i = 0; i < valueArray.length; i++) {
				value[i] = valueArray[i];
			}
		}
		for (var i = 0; i < value.length; i++) {
			for (var j = 0; j < list.options.length; j++) {
				if (list.options[j].value == value[i]) {
					list.options[j].selected = true;
				}
			}
		}
	}
</script>
</head>
<%
    String driverClass = request.getParameter("driverClass");
			String jdbcUrl = request.getParameter("jdbcUrl");
			String userName = request.getParameter("userName");
			String password = request.getParameter("password");
			String optType = request.getParameter("optType");
			String pageSize = request.getParameter("pageSize");
			String pageNumber = request.getParameter("pageNumber");
			String sql = request.getParameter("sql");
			DBToolBOImpl dbToolBO = new DBToolBOImpl();
			if (StringUtils.isNotBlank(sql) && StringUtils.isNotBlank(userName) && StringUtils.isNotBlank(password)) {
				dbToolBO.setDriverClass(driverClass);
				dbToolBO.setJdbcUrl(jdbcUrl);
				dbToolBO.setUserName(userName);
				dbToolBO.setPassword(password);
				dbToolBO.setOptType(optType);
				dbToolBO.setPageSize(pageSize);
				dbToolBO.setPageNumber(pageNumber);
				dbToolBO.setSql(sql);
				Object executeResult = null;
				try {
					executeResult = dbToolBO.execute();
				} catch (Exception e) {
					request.setAttribute("promptInformation", e.getMessage());
				}
				request.setAttribute("result", executeResult);
			}
%>
<body>
	<table>
		<c:if test="${not empty promptInformation}">
			<tr>
				<td style="color: #FF0000;">${promptInformation}</td>
			</tr>
		</c:if>
		<tr>
			<td>
				<form id="frmDBTool" name="frmDBTool" method="post" action="${pageContext.request.contextPath}/dbtool/execute.jsp">
					<input type="hidden" name="method" id="method" value="execute" />
					<table border="0" width="100%" cellpadding="4" cellspacing="0" align="left">
						<tr>
							<td width="100">数据库驱动:</td>
							<td>
								<input type="text" name="driverClass" id="driverClass" size="50" value="${param.driverClass}" readOnly="true" />
							</td>
						</tr>
						<tr>
							<td>JDBC URL:</td>
							<td>
								<input type="text" name="jdbcUrl" id="jdbcUrl" size="50" value="${param.jdbcUrl}" />
								<a href="javascript:setHTMLElementValue('jdbcUrl', 'jdbc:mysql://127.0.0.1:3306/test');setHTMLElementValue('driverClass', 'com.mysql.jdbc.Driver');">MySQL</a>
								<a href="javascript:setHTMLElementValue('jdbcUrl', 'jdbc:oracle:thin:@127.0.0.1:1521:orcl');setHTMLElementValue('driverClass', 'oracle.jdbc.driver.OracleDriver');">Oracle </a>
							</td>
						</tr>
						<tr>
							<td>用户名:</td>
							<td>
								<input type="text" name="userName" id="userName" value="${param.userName}" />
							</td>
						</tr>
						<tr>
							<td>密码:</td>
							<td>
								<input type="password" name="password" id="password" value="${param.password}" />
							</td>
						</tr>
						<tr>
							<td>操作类型:</td>
							<td>
								<select name="optType" id="optType" onchange="onOptTypeChange();">
									<option value="R">查询</option>
									<option value="C">插入</option>
									<option value="U">更新</option>
									<option value="D">删除</option>
								</select>
								<span id="spanPageSize" style="display: none;">
									<br />每页条数:
									<select name="pageSize" id="pageSize" onchange="onPageSizeChange();">
										<option value="5">5 条</option>
										<option value="10">10 条</option>
										<option value="20">20 条</option>
										<option value="50">50 条</option>
										<option value="100">100 条</option>
									</select>
								</span>
								<span id="spanPageNumber" style="display: none;">
									<br />跳转到
									<input type="number" name="pageNumber" id="pageNumber" size="5" value="${param.pageNumber}" />
									页
								</span>
								<br />
								<input type=button value=" test " onClick="frmDBTool.sql.value='select 1 from dual';">
								<input type=button value=" where " onClick="frmDBTool.sql.value=frmDBTool.sql.value+this.value;">
								<input type=button value=" order by " onClick="frmDBTool.sql.value=frmDBTool.sql.value+this.value;">
								<input type=button value=" desc " style="width: 50;" onClick="frmDBTool.sql.value=frmDBTool.sql.value+this.value;">
								<input type=button value=" , " style="width: 45;" onClick="frmDBTool.sql.value=frmDBTool.sql.value+this.value;">
								<input type=button value=" cls " style="width: 55;" onClick="frmDBTool.sql.value='select * from ';">
								<input type=button value=" dual " style="width: 55;" onClick="frmDBTool.sql.value='select 1 from dual';">
								<input type=button value=" delete " onClick="frmDBTool.sql.value='delete from ';">
								<input type=button value=" describe " onClick="frmDBTool.sql.value='describe ';">
							</td>
						</tr>
						<tr>
							<td valign="top">SQL指令:</td>
							<td>
								<textarea name="sql" id="sql" cols="100" rows="8" onkeypress="this.value = keywordToUpper(this.value);">${param.sql}</textarea>
							</td>
						</tr>
						<tr>
							<td valign="top"></td>
							<td valign="middle">
								<input type="button" name="btnSubmit" id="btnSubmit" value=" 执  行 " onclick="doExecute();" />
							</td>
						</tr>
					</table>
				</form>
			</td>
		</tr>
		<tr>
			<td>

				<!-- 处理数据库操作结果 -->
				<c:if test="${not empty param.optType}">
					<c:choose>
						<c:when test="${param.optType eq 'C'}">
							<%-- 新增操作 --%>
							<c:if test="${result ne null}">
						新增记录,影响数据库记录行数: ${result}
					</c:if>
						</c:when>
						<c:when test="${param.optType eq 'R'}">
							<%-- 查询操作 --%>
							<c:if test="${not empty result}">
								<c:forEach var="map" items="${result}">
									<c:set var="titles" value="${map.key}" />
									<!-- 表头的标题 -->
									<c:set var="dataList" value="${map.value}" />
									<!-- 数据二维矩阵对象 -->
									<table width="100%" border="1" cellpadding="4" cellspacing="0" align="left" bordercolordark="#FFFFFFF" bordercolorlight="#EEEEEE">
										<tr>
											<c:forEach var="title" items="${titles}">
												<th>${title}</th>
											</c:forEach>
										</tr>
										<c:if test="${not empty dataList}">
											<c:forEach var="datas" items="${dataList}">
												<tr>
													<c:forEach var="data" items="${datas}">
														<td>${data}&nbsp;</td>
													</c:forEach>
												</tr>
											</c:forEach>
										</c:if>
									</table>
								</c:forEach>
							</c:if>
						</c:when>
						<c:when test="${param.optType eq 'U'}">
							<%-- 更新操作 --%>
							<c:if test="${result ne null}">
						更新记录,影响数据库记录行数: ${result}
					</c:if>
						</c:when>
						<c:when test="${param.optType eq 'D'}">
							<%-- 删除操作 --%>
							<c:if test="${result ne null}">
						删除记录,影响数据库记录行数: ${result}
					</c:if>
						</c:when>
					</c:choose>
				</c:if>
			</td>
		</tr>
	</table>

	<script type="text/javascript">
		setListValue('optType', [ '${param.optType}' ]);
		setListValue('pageSize', [ '${param.pageSize}' ]);
		onOptTypeChange();
		onPageSizeChange();
		document.body.onkeypress = function(e) {
			if (!e) {
				e = window.event;
				if (e.ctrlKey) {
					var keyCode = e.keyCode;
					if (keyCode == 7) {
						if (confirm('确定执行数据库指令吗?')) {
							doExecute();
						}
					}
				}
			}
		}

		if (navigator.userAgent.indexOf('MSIE') != -1) {
			document.getElementById('tip1td').innerHTML = '<font color="#0000FF">小提示:&nbsp;同时按下Ctrl + G键,快速执行数据库指令</font>';
		}

		keywordToUpper(document.getElementById('sql').value);
	</script>
</body>
</html>